from fastapi import params
from sqlalchemy import DECIMAL, Column, Integer, String, TIMESTAMP, Text, DateTime
from sqlalchemy.sql import func
from database import Base
from sqlalchemy import Float 
from sqlalchemy.orm import Session
from datetime import datetime, timedelta
from passlib.context import CryptContext
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import joinedload
from core.logger import app_logger as logger

#所有model的父类
class MjModel(Base):
    __abstract__ = True                                                 
    CreateTime = Column(DateTime, server_default=func.now(), nullable=False,comment="创建时间")
    UpdateTime = Column(TIMESTAMP, server_default=func.now(), onupdate=func.now(), nullable=False,comment="更新时间")
    DeadTime = Column(TIMESTAMP, nullable=True, comment="失效时间(软删除标记)")

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

#Url生成类
class URLGenerator:
    @staticmethod
    def generate_live_url(media_user, media_password, media_ip, media_port, media_num, media_type):
        """生成实时视频流URL"""
        if media_type == 1:
            return "-"
        elif media_type == 0:
            return f"rtsp://{media_user}:{media_password}@{media_ip}:{media_port}/Streaming/Channels/{media_num}01"
        else:
            return f"rtsp://{media_user}:{media_password}@{media_ip}:{media_port}/Streaming/Channels/{media_num}01"
    
    @staticmethod
    def convert_time(time_str):
        """转换时间格式：YYYY-MM-DD HH:MM:SS -> YYYYMMDDTHHMMSSZ"""
        ymd, hms = time_str.split(" ")
        return ymd.replace("-", "") + "T" + hms.replace(":", "") + "Z"
    
    @staticmethod
    def generate_playback_url(media_user, media_password, media_ip, media_port, media_num, start_time):
        """生成回放URL"""
        start_time_converted = URLGenerator.convert_time(start_time)
        return f"rtsp://{media_user}:{media_password}@{media_ip}:{media_port}/Streaming/tracks/{media_num}01?starttime={start_time_converted}"

class User(MjModel):
    __tablename__ = "user"
    __table_args__ = ({'comment': '用户表'})
    UserId = Column(Integer, primary_key=True, index=True, comment="用户ID(主键)")
    UserName = Column(String(50), index=True,unique=True, nullable=False, comment="用户名(唯一)")
    Password = Column(String(255), nullable=False, comment="密码")
    RealName = Column(String(50), nullable=False, comment="真实姓名")
    Role = Column(String(20), default='user', nullable=False, comment="用户角色")
    Contact = Column(String(50), nullable=True, comment="联系方式")
    def set_password(self, password: str):
        """哈希并设置密码"""
        self.Password = pwd_context.hash(password)
    
    def verify_password(self, plain_password: str) -> bool:
        """验证密码"""
        return pwd_context.verify(plain_password, self.Password)
    
class Alarm(MjModel):
    __tablename__ = "alarm"
    __table_args__ = ({'comment': '告警信息表'})
    AlarmId = Column(Integer, primary_key=True, index=True, comment="告警ID(主键)")
    BoardIp = Column(String(50), nullable=False, comment="设备IP地址")
    BoardId = Column(String(50), nullable=False, comment="设备唯一标识")
    DataId = Column(Integer, comment="数据ID")
    Type = Column(Integer, comment="告警类型")
    Summary = Column(String(100), comment="告警摘要")
    Time = Column(String(50), comment="告警时间(原始格式)")
    TimeStamp = Column(String(50), comment="告警时间戳")
    TaskDesc = Column(String(50), comment="任务描述")
    TaskSession = Column(String(50), comment="任务会话ID")
    ResultType = Column(String(50), comment="结果类型")
    ResultDescription = Column(String(100), comment="结果详细描述")
    PeopleNumber = Column(Integer, comment="检测到的人数")
    ImagePath = Column(String(50), comment="关联图片存储路径")
    PassageId = Column(Integer, ForeignKey('passage.PassageId'), nullable=True, comment="关联的通道ID")
    
    # 添加与Passage的关系
    passage = relationship("Passage", backref="alarms")

    # 添加一个属性来访问关联通道的MediaUrl
    @property
    def PlaybackURL(self):
        if self.passage:
            # 格式化时间（假设Time字段格式为"%Y-%m-%d %H:%M:%S"）
            starttime = (self.CreateTime - timedelta(seconds=15)).strftime("%Y-%m-%d %H:%M:%S")
            
            return URLGenerator.generate_playback_url(
                self.passage.MediaUser,
                self.passage.MediaPassword,
                self.passage.MediaIp,
                self.passage.MediaPort,
                self.passage.MediaNum,
                starttime
            )
        return None
    def to_dict(self):
        data = {c.name: getattr(self, c.name) for c in self.__table__.columns}
        # 添加通道信息
        if self.passage:
            data['passage'] = {
                'MediaDesc': self.passage.MediaDesc,
                # 可以添加其他需要的通道字段
            }
        else:
            data['passage'] = None
        return data
    # def PlaybackURL(self, db_session):  # 移除类型注解
    #     """
    #     构建回放URL
    #     格式: rtsp://{nvr_user}:{nvr_password}@{nvr_ip}:{nvr_port}/Streaming/tracks/{channel_part}?starttime={starttime}
    #     """
    #     # 默认值（当参数表中没有对应配置时使用）
    #     params = {
    #         "NVR_USER_NAME": "admin1",
    #         "NVR_PASSWORD": "aa147258",
    #         "NVR_IP": "192.168.10.200",
    #         "NVR_PORT": "554"
    #     }
    #     logger.info("执行了这里。")
    #     logger.debug(hasattr(db_session, 'query'))
    #     logger.debug(db_session)
    #     # 如果提供了数据库会话，尝试从parameter表中获取最新值
    #     if db_session and hasattr(db_session, 'query'):  # 检查是否是有效的会话
    #         try:
    #             # 获取所有需要的参数
    #             param_names = ["NVR_USER_NAME", "NVR_PASSWORD", "NVR_IP", "NVR_PORT"]
    #             db_params = db_session.query(Parameter).filter(
    #                 Parameter.ParameterName.in_(param_names)
    #             ).all()
                
    #             # 更新参数值
    #             for param in db_params:
    #                 params[param.ParameterName] = param.ParameterValue
    #         except Exception as e:
    #             print(f"获取回放URL参数时出错: {str(e)}")
    #             # 出错时使用默认值
        
    #     logger.debug(f"获取参数："+params['NVR_USER_NAME'])

    #     # 处理TaskSession - 去掉第一个字母
    #     channel_part = self.TaskSession[1:] if self.TaskSession else "01"
        
    #     # 格式化时间（假设Time字段格式为"%Y-%m-%d %H:%M:%S"）
    #     starttime = (self.CreateTime - timedelta(seconds=15)).strftime("%Y-%m-%d %H:%M:%S")

    #     def convert_time(time_str):
    #         """转换时间格式：YYYY-MM-DD HH:MM:SS -> YYYYMMDDTHHMMSSZ"""
    #         ymd, hms = time_str.split(" ")
    #         return ymd.replace("-", "") + "T" + hms.replace(":", "") + "Z"
        
    #     start_time = convert_time(starttime)
        
    #     # 构建完整URL
    #     return (
    #         f"rtsp://{params['NVR_USER_NAME']}:{params['NVR_PASSWORD']}@"
    #         f"{params['NVR_IP']}:{params['NVR_PORT']}/"
    #         f"Streaming/tracks/{channel_part}01?starttime={start_time}"
    #     )

class Device(MjModel):
    __tablename__ = "device"
    __table_args__ = ({'comment': '设备信息表'})
    DeviceId = Column(Integer, primary_key=True, index=True, comment="设备记录ID(主键)")
    BoardId = Column(String(100), nullable=False, unique=True, comment="设备唯一标识")          
    BoardIp = Column(String(15), nullable=False, unique=True,comment="设备IP地址")                        
    BoardPlatform = Column(String(50), nullable=False, comment="设备平台")                   
    BoardTemp = Column(String(20), nullable=False, comment="设备温度")                       
    BoardType = Column(String(20), nullable=False, comment="设备类型")                       
    GBClientId = Column(String(50), comment="国标客户端ID")                                      
    GrantCode = Column(Integer, nullable=False, comment="授权代码")                          
    GrantDesc = Column(String(50), nullable=False, comment="授权描述")                       
    HostDiskAvailable = Column(Integer, nullable=False, comment="磁盘可用空间(MB)")                  
    HostDiskTotal = Column(Integer, nullable=False, comment="磁盘总空间(MB)")                     
    HostDiskUsed = Column(Integer, nullable=False, comment="磁盘已用空间(MB)")                      
    HostMemory0 = Column(Float, nullable=False, comment="内存使用率(百分比)")                          
    HostMemory1 = Column(Float, nullable=False, comment="内存总量(GB)")                          
    Key = Column(String(20), nullable=False, comment="运行状态密钥")                             
    Status = Column(String(20), nullable=False, comment="在线状态(online/offline)")                          
    
class Parameter(MjModel):
    __tablename__ = "parameter"
    __table_args__ = ({'comment': '参数配置表'})
    ParameterId = Column(Integer, primary_key=True, index=True, comment="参数ID(主键)")
    ParameterName = Column(String(100), nullable=False, unique=True, comment="参数名称")
    DeviceParameter = Column(String(100), nullable=True,  comment="边缘设备参数名称")
    ParameterValue = Column(String(255), nullable=False, comment="参数值")
    ParameterDescription = Column(String(100), nullable=False, comment="参数描述")
    BoardId = Column(String(100), nullable=True, comment="关联的设备ID") 

class Passage(MjModel):  #通道表
    __tablename__ = 'passage'
    __table_args__ = ({'comment': '通道管理表'})
    PassageId = Column(Integer, nullable=False,primary_key=True)
    MediaName = Column(String(255),nullable=False, comment='通道唯一标识')
    MediaDesc = Column(String(255),nullable=False, comment='通道描述')
    #MediaUrl = Column(String(255),nullable=False, comment='视频流地址')
    MediaType = Column(Integer,nullable=False, comment='通道类型')
    MediaIp = Column(String(20),nullable=False, comment='nvr或摄像头ip')
    MediaPort = Column(Integer,nullable=False, comment='nvr或摄像头端口')
    MediaUser = Column(String(20),nullable=False, comment='nvr或摄像头用户名')
    MediaPassword = Column(String(20),nullable=False, comment='nvr或摄像头密码')
    MediaNum = Column(String(10),nullable=False, comment='通道号')
    MediaStatus = Column(String(10),nullable=True, comment='摄像头状态')
    RtspTransport = Column(String(1), nullable=True,comment='是否开启RTSP转发')
    GBTransport = Column(String(1), nullable=True,comment='是否开启GB28181转发')
    SubId = Column(String(255), nullable=True,comment='国际通道号（如果开启GB转发）')
    BoardId = Column(String(255), nullable=False,comment='盒子ID')
    BoardIp = Column(String(255), nullable=False,comment='设备IP地址')
    AreaId = Column(Integer, nullable=False,comment='区域ID')

    @hybrid_property
    def MediaUrl(self):
        """动态生成RTSP URL"""
        return URLGenerator.generate_live_url(
            self.MediaUser, 
            self.MediaPassword, 
            self.MediaIp, 
            self.MediaPort, 
            self.MediaNum, 
            self.MediaType
        )

class Task(MjModel):     #任务表
    __tablename__ = 'task'
    __table_args__ = ({'comment': '任务管理表'})
    TaskId = Column(Integer, primary_key=True, nullable=False,comment='任务id')
    AlgTaskSession = Column(String(255), nullable=False,comment='任务标识')
    TaskDesc = Column(String(255), comment='任务描述')
    AlgInfo = Column(String(255),nullable=False, comment='运行的主算法')
    MediaName = Column(String(255), nullable=False,comment='使用的媒体通道号')
    MetadataUrl = Column(String(255),nullable=False, comment='接受告警的目标地址')
   

class Algorithm(MjModel):    #算法表
    __tablename__ = 'algorithm'
    __table_args__ = ({'comment': '算法管理表'})
    AlgorithmID = Column(Integer, primary_key=True, comment='算法id')
    AlgorithmName = Column(String(255), comment='算法名称')
     
class ElectFence(MjModel):   #电子围栏设备表
    __tablename__ = "elect_fence"
    __table_args__ = ({'comment': '电子围栏表'})
    FenceId = Column(Integer, primary_key=True, autoincrement=True, comment="电子围栏编号")
    FenceName = Column(String(255), nullable=False, unique=True, comment="电子围栏标识唯一")
    FenceDesc = Column(String(255), nullable=True, comment="电子围栏位置")
    FenceState = Column(String(10), nullable=False, comment="在线状态")
    Electricity = Column(String(20), nullable=True, comment="电量")
    AreaId = Column(Integer, nullable=False,comment='区域ID')

class EFAlarm(MjModel):     #电子围栏报警信息表
    __tablename__ = "ef_alarm"
    __table_args__ = ({'comment': '电子围栏报警表'})
    EFAlarmID = Column(Integer, primary_key=True, autoincrement=True, comment="电子围栏报警id")
    FenceName = Column(String(255), nullable=False, comment="发送报警的设备名称")
    StartTime = Column(DateTime, nullable=False, comment="报警开始时间")
    EndTime = Column(DateTime, nullable=True, comment="报警结束时间")
    FenceState = Column(String(10), nullable=True, comment="报警状态")
    FenceLocation = Column(String(255), nullable=True, comment="电子围栏位置") 


class FaceLibrary(MjModel):
    __tablename__ = "facelibrary"
    __table_args__ = ({'comment': '人脸库表'})
    LibraryId = Column(Integer, primary_key=True, autoincrement=True, comment="人脸库ID")
    LibraryName = Column(String(100), unique=True, nullable=False, comment="人脸库名称")

class FaceInfo(MjModel):
    __tablename__ = "face"
    __table_args__ = ({'comment': '人脸信息表'})
    FaceId = Column(Integer, primary_key=True, autoincrement=True, comment="人脸ID")
    UserId = Column(String(100), nullable=False, comment="用户标识")
    UserName = Column(String(100), nullable=False, comment="姓名")
    LibraryId = Column(Integer, ForeignKey('facelibrary.LibraryId'), nullable=False, comment="所属人脸库ID")
    RegisterTime = Column(DateTime, server_default=func.now(), nullable=False, comment="注册日期")
    ImagePath = Column(String(255), nullable=False, comment="注册图片存储路径")
    PhotoId = Column(Integer, nullable=True, comment="设备返回的人脸ID") 
    BirthYear = Column(Integer, nullable=True, comment="出生年份")  
    Gender = Column(String(10), nullable=True, comment="性别")     
    # 建立关系，方便查询
    library = relationship("FaceLibrary", backref="faces")    

class DeviceFaceMapping(MjModel):
    __tablename__ = "device_face_mapping"
    __table_args__ = ({'comment': '设备人脸映射表'})
    MappingId = Column(Integer, primary_key=True, autoincrement=True, comment="映射ID")
    FaceId = Column(Integer, ForeignKey('face.FaceId'), nullable=False, comment="人脸ID")
    DeviceId = Column(Integer, ForeignKey('device.DeviceId'), nullable=False, comment="设备ID")
    PhotoId = Column(Integer, nullable=False, comment="设备上的人脸ID")
    LibraryName = Column(String(100), nullable=False, comment="设备上的人脸库名称")
    
    # 建立关系
    face = relationship("FaceInfo", backref="device_mappings")
    device = relationship("Device", backref="face_mappings")
    
class RegionRiskLevel(MjModel):
    __tablename__ = "region_risk_level"
    __table_args__ = ({'comment': '区域风险等级表'})
    
    id = Column(Integer, primary_key=True, autoincrement=True, comment="主键ID")
    record_id = Column(String(36), nullable=False, unique=True, comment="记录唯一标识")
    device_name = Column(String(100), nullable=True, comment="设备名称")
    location = Column(String(100), nullable=True, comment="区域位置")
    risk_score = Column(DECIMAL(8,4), nullable=True, comment="风险分数")
    risk_level = Column(String(20), nullable=True, comment="风险等级")
    alarm_count = Column(Integer, nullable=True, comment="报警次数")
    total_time_weight = Column(DECIMAL(8,4), nullable=True, comment="总时间权重")
    last_alarm_date = Column(DateTime, nullable=True, comment="最后报警时间")
    update_time = Column(DateTime, nullable=True, comment="更新时间")
    created_time = Column(DateTime, server_default=func.now(), comment="创建时间")

class HourlyRiskDistribution(MjModel):
    __tablename__ = "hourly_risk_distribution"
    __table_args__ = ({'comment': '小时风险分布表'})
    
    id = Column(Integer, primary_key=True, autoincrement=True, comment="主键ID")
    record_id = Column(String(36), nullable=False, unique=True, comment="记录唯一标识")
    device_name = Column(String(100), nullable=True, comment="设备名称")
    location = Column(String(100), nullable=True, comment="区域位置")
    update_time = Column(DateTime, nullable=True, comment="更新时间")
    created_time = Column(DateTime, server_default=func.now(), comment="创建时间")
    
    # 为每个小时定义一个字段，从h00_risk到h23_risk
    h00_risk = Column(DECIMAL(6,4), default=0, comment="00时风险值")
    h01_risk = Column(DECIMAL(6,4), default=0, comment="01时风险值")
    h02_risk = Column(DECIMAL(6,4), default=0, comment="02时风险值")
    h03_risk = Column(DECIMAL(6,4), default=0, comment="03时风险值")
    h04_risk = Column(DECIMAL(6,4), default=0, comment="04时风险值")
    h05_risk = Column(DECIMAL(6,4), default=0, comment="05时风险值")
    h06_risk = Column(DECIMAL(6,4), default=0, comment="06时风险值")
    h07_risk = Column(DECIMAL(6,4), default=0, comment="07时风险值")
    h08_risk = Column(DECIMAL(6,4), default=0, comment="08时风险值")
    h09_risk = Column(DECIMAL(6,4), default=0, comment="09时风险值")
    h10_risk = Column(DECIMAL(6,4), default=0, comment="10时风险值")
    h11_risk = Column(DECIMAL(6,4), default=0, comment="11时风险值")
    h12_risk = Column(DECIMAL(6,4), default=0, comment="12时风险值")
    h13_risk = Column(DECIMAL(6,4), default=0, comment="13时风险值")
    h14_risk = Column(DECIMAL(6,4), default=0, comment="14时风险值")
    h15_risk = Column(DECIMAL(6,4), default=0, comment="15时风险值")
    h16_risk = Column(DECIMAL(6,4), default=0, comment="16时风险值")
    h17_risk = Column(DECIMAL(6,4), default=0, comment="17时风险值")
    h18_risk = Column(DECIMAL(6,4), default=0, comment="18时风险值")
    h19_risk = Column(DECIMAL(6,4), default=0, comment="19时风险值")
    h20_risk = Column(DECIMAL(6,4), default=0, comment="20时风险值")
    h21_risk = Column(DECIMAL(6,4), default=0, comment="21时风险值")
    h22_risk = Column(DECIMAL(6,4), default=0, comment="22时风险值")
    h23_risk = Column(DECIMAL(6,4), default=0, comment="23时风险值")
        
class Area(MjModel):
    __tablename__ = "areas"
    __table_args__ = ({'comment': '区域信息表'})
    
    AreaId = Column(Integer, primary_key=True, autoincrement=True, index=True, comment="区域ID")
    AreaName = Column(String(255), nullable=False, unique=True, comment="区域名称")
    Latitude = Column(DECIMAL(10, 8), nullable=True, comment="纬度")
    Longitude = Column(DECIMAL(11, 8), nullable=True, comment="经度")


